package wang.dao;

import wang.bean.TouristAttraction;
import wang.utils.JDBCUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

public class TouristAttractionDao {
  public static final String FIELD_ID = "id";
  public static final String FIELD_TITLE = "title";
  public static final String FIELD_THEME = "theme";
  public static final String FIELD_DESCRIPTION = "description";
  public static final String FIELD_COUNTRY = "country";
  public static final String FIELD_ADDRESS = "address";


  // 新增管理员
  public void insert(TouristAttraction touristAttraction) {
    Connection conn = null;
    PreparedStatement ps = null;
    ResultSet rs = null;
    try {
      // 获得数据的连接
      conn = JDBCUtils.getConnection();

      // 发送SQL语句
      String sql = "insert into t_tourist_attraction(title, description, country, address) values(?, ?, ?, ?)";

      ps = conn.prepareStatement(sql);
      ps.setString(1, touristAttraction.getTitle());
      ps.setString(2, touristAttraction.getDescription());
      ps.setString(3, touristAttraction.getCountry());
      ps.setString(4, touristAttraction.getAddress());
      ps.executeUpdate();
    } catch (Exception e) {
      e.printStackTrace();
    } finally {
      JDBCUtils.release(rs, ps, conn);
    }
  }

  // 删除管理员
  public void delete(int id) {
    Connection conn = null;
    Statement stmt = null;
    ResultSet rs = null;
    try {
      conn = JDBCUtils.getConnection();
      stmt = conn.createStatement();
      String sql = "DELETE FROM t_tourist_attraction WHERE id = " + id;
      stmt.executeUpdate(sql);
    } catch (Exception e) {
      e.printStackTrace();
    } finally {
      JDBCUtils.release(rs, stmt, conn);
    }
  }

  // 修改管理员
  public boolean update(TouristAttraction touristAttraction) {
    Connection conn = null;
    PreparedStatement ps = null;
    ResultSet rs = null;
    try {
      conn = JDBCUtils.getConnection();
      String sql = "update t_tourist_attraction set title = ?, description = ?, country = ?, address = ? where id = ?";
      ps = conn.prepareStatement(sql);
      ps.setString(1, touristAttraction.getTitle());
      ps.setString(2, touristAttraction.getDescription());
      ps.setString(3, touristAttraction.getCountry());
      ps.setString(4, touristAttraction.getAddress());
      ps.setInt(5, touristAttraction.getId());
      int num = ps.executeUpdate();
      return num > 0;
    } catch (Exception e) {
      e.printStackTrace();
    } finally {
      JDBCUtils.release(rs, ps, conn);
    }
    return false;
  }

  // 获取管理员
  public TouristAttraction get(int id) {
    Connection conn = null;
    Statement stmt = null;
    ResultSet rs = null;
    try {
      // 获得数据的连接
      conn = JDBCUtils.getConnection();
      // 获得Statement对象
      stmt = conn.createStatement();
      // 发送SQL语句
      String sql = "SELECT * FROM t_tourist_attraction WHERE id = " + id;
      rs = stmt.executeQuery(sql);
      // 处理结果集
      while (rs.next()) {
        TouristAttraction touristAttraction = new TouristAttraction();
        touristAttraction.setId(rs.getInt(FIELD_ID));
        touristAttraction.setTitle(rs.getString(FIELD_TITLE));
        touristAttraction.setTheme(rs.getString(FIELD_THEME));
        touristAttraction.setDescription(rs.getString(FIELD_DESCRIPTION));
        touristAttraction.setCountry(rs.getString(FIELD_COUNTRY));
        touristAttraction.setAddress(rs.getString(FIELD_ADDRESS));
        return touristAttraction;
      }
      return null;
    } catch (Exception e) {
      e.printStackTrace();
    } finally {
      JDBCUtils.release(rs, stmt, conn);
    }
    return null;
  }

  public List<TouristAttraction> list() {
    Connection conn = null;
    Statement stmt = null;
    ResultSet rs = null;

    List<TouristAttraction> touristAttractions = new ArrayList<>();
    try {
      // 获得数据的连接
      conn = JDBCUtils.getConnection();
      // 获得Statement对象
      stmt = conn.createStatement();
      // 发送SQL语句
      String sql = "SELECT * FROM t_tourist_attraction";
      rs = stmt.executeQuery(sql);
      // 处理结果集
      while (rs.next()) {
        TouristAttraction touristAttraction = new TouristAttraction();
        touristAttraction.setId(rs.getInt(FIELD_ID));
        touristAttraction.setTitle(rs.getString(FIELD_TITLE));
        touristAttraction.setTheme(rs.getString(FIELD_THEME));
        touristAttraction.setDescription(rs.getString(FIELD_DESCRIPTION));
        touristAttraction.setCountry(rs.getString(FIELD_COUNTRY));
        touristAttraction.setAddress(rs.getString(FIELD_ADDRESS));
        touristAttractions.add(touristAttraction);
      }
      return touristAttractions;
    } catch (Exception e) {
      e.printStackTrace();
    } finally {
      JDBCUtils.release(rs, stmt, conn);
    }
    return new ArrayList<>();
  }
}
